/**
 * 
 */
package org.demo.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.demo.dao.ICstServiceDAO;
import org.demo.entity.CstLost;
import org.demo.entity.CstService;
import org.demo.entity.Orders;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
 * @author GeorgeHuang
 *
 */
public class CstServiceDAOImpl extends HibernateDaoSupport implements
		ICstServiceDAO {
	public void save(CstService transientInstance) {
		 
		try {
			getHibernateTemplate().save(transientInstance);
		 
		} catch (RuntimeException re) {
			 
			throw re;
		}
	}
	
 
	
	public List findCstService(final int pageNo, final int pageSize,final String svrStatus,final CstService service,final String beginDate,final String endDate) {
		 
		return (List) getHibernateTemplate().execute(new HibernateCallback(){
			
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				 Date begin=null;
				 Date end=null;
				String hql = "select o from CstService o where o.svrCustName like :svrCustName" +
						" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				String status= svrStatus;
				
				if (status!=null && status!="") {
					String theStatus = null;
					 
						theStatus = status;
					 
					hql = "select o from CstService o where o.svrStatus='"+theStatus+"' and o.svrCustName like :svrCustName" +
					" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				}else if (service.getSvrStatus()!=null && service.getSvrStatus()!="") {
					hql = "select o from CstService o where o.svrStatus='"+service.getSvrStatus()+"' and o.svrCustName like :svrCustName" +
					" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				}
				
				if (beginDate!="" && endDate=="") {
					try {
						begin = dateFormat.parse(beginDate);
						 
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					hql+=" and o.svrCreateDate >= :begin";
				}
				if (beginDate=="" &&  endDate!="") {
					try {
					 
						end=dateFormat.parse(endDate);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					hql+=" and o.svrCreateDate <= :end";
				}
				if (beginDate!="" && beginDate!=null &&  endDate!="" &&endDate!=null) {
					try {
						begin = dateFormat.parse(beginDate);
						end=dateFormat.parse(endDate);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					hql+=" and o.svrCreateDate >= :begin and o.svrCreateDate <= :end"; 
				}
				
				//and o.svrCreateDate between ':svrCreateDate1' and ':svrCreateDate2'
			 
				Query query = s.createQuery(hql); 
				query.setString("svrCustName", "%"+service.getSvrCustName()+"%");
				query.setString("svrTitle", "%"+service.getSvrTitle()+"%");
				query.setString("svrType", "%"+service.getSvrType()+"%");
				if (beginDate!="" && endDate=="") {
					
					query.setTimestamp("begin", begin);
				}
				if (beginDate=="" &&  endDate!="") {
					
					query.setTimestamp("end", end);
				}
				if (beginDate!="" && beginDate!=null &&  endDate!="" &&endDate!=null) {
					query.setTimestamp("begin", begin);
					query.setTimestamp("end", end);
				}
				int firstResultIndex=pageSize*(pageNo-1);
		 
				query.setFirstResult(firstResultIndex);
				query.setMaxResults(pageSize);
				
				
				return query.list();
			}});
		 
	}


	public Integer countCstService(final int pageNo, final int pageSize,final String svrStatus,final CstService service,final String beginDate,final String endDate) throws UnsupportedEncodingException {
		 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				String hql = "select count(o) from CstService o where o.svrCustName like :svrCustName" +
						" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				
				String status= svrStatus;
				
				if (status!=null && status!="") {
					String theStatus=  status;
					hql = "select count(o) from CstService o where o.svrStatus='"+theStatus+"' and o.svrCustName like :svrCustName" +
					" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				}else if (service.getSvrStatus()!=null && service.getSvrStatus()!="") {
					hql = "select count(o) from CstService o where o.svrStatus='"+service.getSvrStatus()+"' and o.svrCustName like :svrCustName" +
					" and o.svrTitle like :svrTitle and o.svrType like :svrType";
				}
				List  list=new ArrayList();
				Date begin = null;
				Date end;
				try {
					if (beginDate!=null && beginDate!="" && endDate=="") {
						begin= dateFormat.parse(beginDate);
						hql+=" and o.svrCreateDate >= :begin";
						list = getHibernateTemplate().findByNamedParam(hql, new String[]{"svrCustName","svrTitle","svrType","begin"},
								new Object[]{"%"+service.getSvrCustName()+"%","%"+service.getSvrTitle()+"%","%"+service.getSvrType()+"%",begin});
						 
					}else if (beginDate=="" &&  endDate!=null && endDate!="") {
						end= dateFormat.parse(endDate);
						hql+=" and o.svrCreateDate <= :end";
						list = getHibernateTemplate().findByNamedParam(hql, new String[]{"svrCustName","svrTitle","svrType","end"},
								new Object[]{"%"+service.getSvrCustName()+"%","%"+service.getSvrTitle()+"%","%"+service.getSvrType()+"%",end});
						 
					}else if (beginDate!="" && beginDate!=null &&  endDate!="" &&endDate!=null) {
						begin= dateFormat.parse(beginDate);
						end= dateFormat.parse(endDate);
						hql+=" and o.svrCreateDate >=  :begin  and o.svrCreateDate <=  :end"; 
						list = getHibernateTemplate().findByNamedParam(hql, new String[]{"svrCustName","svrTitle","svrType","begin","end"},
								new Object[]{"%"+service.getSvrCustName()+"%","%"+service.getSvrTitle()+"%","%"+service.getSvrType()+"%",begin,end});
					}else {
						   list = getHibernateTemplate().findByNamedParam(hql, new String[]{"svrCustName","svrTitle","svrType"},
								new Object[]{"%"+service.getSvrCustName()+"%","%"+service.getSvrTitle()+"%","%"+service.getSvrType()+"%"});
					}
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				 
				
				if (list !=null) {
					return (Integer) list.get(0);
				}
				
			 return 0;
		 
	}
	
	public List<CstService> findCstServices(String year) {
		 
	 
	
		 
		 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<CstService> list=null;
		
		StringBuffer hql=new StringBuffer("from CstService ");
			 
				 if (year!=null) {
						String year1=year+"-01-01 00:00:00";
						String year2=year+"-12-31 23:59:59";
					 hql.append("  where svrCreateDate between :year1 and :year2");
					 try {
						return getHibernateTemplate().findByNamedParam(hql.toString(),new String[]{"year1","year2"},new Date[]{dateFormat.parse(year1),dateFormat.parse(year2)});
					} catch (DataAccessException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			 return getHibernateTemplate().find(hql.toString());
		 
	}
	 
	public void updateDispatchCstService(CstService cstService) {
		 
		 getHibernateTemplate().update(cstService);
		 
	}	
	
	public CstService findById(Long id) {
	 
		try {
			CstService instance = (CstService) getHibernateTemplate().get("org.demo.entity.CstService", id);
			return instance;
		} catch (RuntimeException re) {
			 
			throw re;
		}
	}

}
